package com.cheksin.excl;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.cheksin.entity.Goods;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ReadExcelUtil {

	private static final String EXCEL_XLS = ".xls";
	private static final String EXCEL_XLSX = ".xlsx";
	private static final SimpleDateFormat SIMPLE_DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd");

	/**
	 * 读取excel数据
	 * 
	 * @throws Exception
	 *
	 */
	public static List<Goods> readExcelInfo(File excelFile){
		try {
			/*
			 * workbook:工作簿,就是整个Excel文档 sheet:工作表 row:行 cell:单元格
			 */

			// BufferedWriter bw = new BufferedWriter(new FileWriter(new
			// File(url)));
			// 支持excel2003、2007
			InputStream is = new FileInputStream(excelFile);// 创建输入流对象
			checkExcelVaild(excelFile);
			Workbook workbook = getWorkBook(is, excelFile);
			// Workbook workbook = WorkbookFactory.create(is);//同时支持2003、2007、2010
			// 获取Sheet数量
			int sheetNum = workbook.getNumberOfSheets();
			// 创建二维数组保存所有读取到的行列数据，外层存行数据，内层存单元格数据
			List<List<String>> dataList = new ArrayList<List<String>>();
			// FormulaEvaluator formulaEvaluator = null;
			// 遍历工作簿中的sheet,第一层循环所有sheet表
			List<Goods> goods = new ArrayList<>();
			for (int index = 0; index < sheetNum; index++) {
				Sheet sheet = workbook.getSheetAt(index);
				if (sheet == null) {
					continue;
				}
//			System.out.println("表单行数：" + sheet.getLastRowNum());
				// 如果当前行没有数据跳出循环，第二层循环单sheet表中所有行
				for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
					Row row = sheet.getRow(rowIndex);
					// 根据文件头可以控制从哪一行读取，在下面if中进行控制
					if (row == null) {
						continue;
					}
					goods.add(getYinBaoGoods(row));

//				// 遍历每一行的每一列，第三层循环行中所有单元格
//				List<String> cellList = new ArrayList<String>();
//				for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
//					Cell cell = row.getCell(cellIndex);
//					System.out.println("遍历行中cell数据:" + getCellValue(cell));
//					cellList.add(getCellValue(cell));
//					System.out.println("第" + cellIndex + "个:     cell个数："
//							+ cellList.size());
//				}
//				dataList.add(cellList);
//				System.out.println("第" + rowIndex + "行:     共几行："
//						+ dataList.size());
				}

			}

			is.close();
			return goods;
		}catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}


	public static Goods getYinBaoGoods(Row row) throws ParseException, NoSuchMethodException, InvocationTargetException, IllegalAccessException {
		Goods good =  new Goods();
		String[] fieldNames = ExportXlsx.fieldName(Goods.class);
		for (int i = 0; i < fieldNames.length; i++) {
			String methodName = "set" + fieldNames[i].substring(0,1).toUpperCase() + fieldNames[i].substring(1);//获取属性的get方法名
			Method method = Goods.class.getDeclaredMethod(methodName,String.class);
			method.invoke(good,getCellValue(row.getCell(i)));//获取属性值
		}
		return good;
	}

	/**
	 * 获取单元格的数据,暂时不支持公式
	 *
	 *
	 */
	public static String getCellValue(Cell cell) {
		int cellType = cell.getCellType();
		String cellValue = "";
		if (cell == null || cell.toString().trim().equals("")) {
			return null;
		}

		if (cellType == Cell.CELL_TYPE_STRING) {
			cellValue = cell.getStringCellValue().trim();
			return cellValue = cellValue == null ? "" : cellValue;
		}
		if (cellType == Cell.CELL_TYPE_NUMERIC) {
			if (HSSFDateUtil.isCellDateFormatted(cell)) { // 判断日期类型
			// cellValue = DateFormatUtil.formatDurationYMD(cell
			// .getDateCellValue().getTime());
			} else { // 否
				cellValue = new DecimalFormat("#.######").format(cell
						.getNumericCellValue());
			}
			return cellValue;
		}
		if (cellType == Cell.CELL_TYPE_BOOLEAN) {
			cellValue = String.valueOf(cell.getBooleanCellValue());
			return cellValue;
		}
		return null;

	}

	/**
	 * 判断excel的版本，并根据文件流数据获取workbook
	 * 
	 * @throws IOException
	 *
	 */
	public static Workbook getWorkBook(InputStream is, File file)
			throws Exception {

		Workbook workbook = null;
		if (file.getName().endsWith(EXCEL_XLS)) {
			workbook = new HSSFWorkbook(is);
		} else if (file.getName().endsWith(EXCEL_XLSX)) {
			workbook = new XSSFWorkbook(is);
		}

		return workbook;
	}

	/**
	 * 校验文件是否为excel
	 * 
	 * @throws Exception
	 *
	 *
	 */
	public static void checkExcelVaild(File file) throws Exception {
		String message = "该文件是EXCEL文件！";
		if (!file.exists()) {
			message = "文件不存在！";
			throw new Exception(message);
		}
		if (!file.isFile()
				|| ((!file.getName().endsWith(EXCEL_XLS) && !file.getName()
						.endsWith(EXCEL_XLSX)))) {
			System.out.println(file.isFile() + "==="
					+ file.getName().endsWith(EXCEL_XLS) + "==="
					+ file.getName().endsWith(EXCEL_XLSX));
			System.out.println(file.getName());
			message = "文件不是Excel";
			throw new Exception(message);
		}
	}

	public static void main(String[] args) throws Exception {
		//System.out.println(readExcelInfo(new File("d://test.xls")));
	}
}